﻿'========================================================================
'功能:郵件參數資料庫存取物件
'作者:andy
'日期: 2011/3/14
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Imports MySql.Data.MySqlClient

Namespace AssetManage.DAO

    Public Class SenMailDAO
        Inherits OAMS.DAO.BaseDAO

        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        Public Sub InsertSendMailParam(ByVal ParamID As String, ByVal MailSubject As String, ByVal MailBody As String, ByVal ReturnDays As String, ByVal ResendDays As String, ByVal ResendTimes As String _
                                         , ByVal UID As String, ByVal ScheduleType As String, ByVal SendDate As String, ByVal TopN As String, ByVal DeptNo1 As String, ByVal DeptNo2 As String, ByVal EquQty As String, _
                                           ByVal AssetType As String, ByVal TakeOfficeDate As String, ByVal HouseID As String, ByVal Status As String, ByVal Monthst As String, ByVal Dayst As String, _
                                           ByVal MailSubjectChn As String, ByVal MailSubjectEng As String, ByVal MailBodyChn As String, ByVal MailBodyEng As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(19) {}
            'sqlParams(0) = New SqlParameter("@ParamID", SqlDbType.NVarChar, ParamID.Length)
            'sqlParams(0).Value = ParamID
            'sqlParams(1) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, MailSubject.Length)
            'sqlParams(1).Value = MailSubject
            'sqlParams(2) = New SqlParameter("@MailBody", SqlDbType.NVarChar, MailBody.Length)
            'sqlParams(2).Value = MailBody
            'sqlParams(3) = New SqlParameter("@ReturnDays", SqlDbType.Int)
            'If ReturnDays = "" Then

            '    sqlParams(3).Value = DBNull.Value
            'Else
            '    sqlParams(3).Value = ReturnDays
            'End If

            'sqlParams(4) = New SqlParameter("@ResendDays", SqlDbType.Int)
            'If ResendDays = "" Then

            '    sqlParams(4).Value = DBNull.Value
            'Else
            '    sqlParams(4).Value = ResendDays
            'End If
            'sqlParams(5) = New SqlParameter("@ResendTimes", SqlDbType.Int)
            'If ResendTimes = "" Then

            '    sqlParams(5).Value = DBNull.Value
            'Else
            '    sqlParams(5).Value = ResendTimes
            'End If

            'sqlParams(6) = New SqlParameter("@UID", SqlDbType.NVarChar, UID.Length)
            'sqlParams(6).Value = UID
            'sqlParams(7) = New SqlParameter("@ScheduleType", SqlDbType.NVarChar, ScheduleType.Length)
            'sqlParams(7).Value = ScheduleType
            'sqlParams(8) = New SqlParameter("@SendDate", SqlDbType.DateTime)
            'If SendDate = "" Then
            '    sqlParams(8).Value = DBNull.Value
            'Else
            '    sqlParams(8).Value = SendDate
            'End If

            'sqlParams(9) = New SqlParameter("@TopN", SqlDbType.Int)
            'If TopN = "" Then
            '    sqlParams(9).Value = DBNull.Value
            'Else
            '    sqlParams(9).Value = TopN
            'End If

            'sqlParams(10) = New SqlParameter("@DeptNo1", SqlDbType.NVarChar, DeptNo1.Length)
            'sqlParams(10).Value = DeptNo1
            'sqlParams(11) = New SqlParameter("@DeptNo2", SqlDbType.NVarChar, DeptNo2.Length)
            'sqlParams(11).Value = DeptNo2
            'sqlParams(12) = New SqlParameter("@EquQty", SqlDbType.Int)
            'If EquQty = "" Then
            '    sqlParams(12).Value = DBNull.Value
            'Else
            '    sqlParams(12).Value = EquQty
            'End If
            'sqlParams(13) = New SqlParameter("@AssetType", SqlDbType.NVarChar, AssetType.Length)
            'sqlParams(13).Value = AssetType


            'sqlParams(14) = New SqlParameter("@TakeOfficeDate", SqlDbType.DateTime)
            'If TakeOfficeDate = "" Then
            '    sqlParams(14).Value = DBNull.Value
            'Else
            '    sqlParams(14).Value = TakeOfficeDate
            'End If

            'sqlParams(15) = New SqlParameter("@HouseID", SqlDbType.NVarChar, HouseID.Length)
            'sqlParams(15).Value = HouseID
            'sqlParams(16) = New SqlParameter("@Status", SqlDbType.NVarChar, Status.Length)
            'sqlParams(16).Value = Status
            'sqlParams(17) = New SqlParameter("@Monthst", SqlDbType.NVarChar, Monthst.Length)
            'sqlParams(17).Value = Monthst
            'sqlParams(18) = New SqlParameter("@Dayst", SqlDbType.NVarChar, Dayst.Length)
            'sqlParams(18).Value = Dayst


            'Dim sb As New StringBuilder()
            'sb.AppendLine("insert into OA_SendMail (ParamID,MailSubject,MailBody,ReturnDays,ResendDays,ResendTimes,CrtUID,CrtDate,ScheduleType,SendDate,TopN,DeptNo1,DeptNo2,EquQty,AssetType,TakeOfficeDate,HouseID,Status,Monthst,Dayst)")
            'sb.AppendLine("values (@ParamID,@MailSubject,@MailBody,@ReturnDays, @ResendDays,@ResendTimes, @UID, getDate(), @ScheduleType, @SendDate, @TopN, @DeptNo1, @DeptNo2, @EquQty,@AssetType, @TakeOfficeDate, @HouseID,@Status,@Monthst,@Dayst)")

            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)



            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                sb.AppendLine(" select max(SerNo)+1 as MAX1 from OA_SendMail ")

                Dim oTable As Data.DataTable
                Dim cmd As MySqlCommand
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                sb.Length = 0
                sb.AppendLine("insert into OA_SendMail (SerNo,ParamID,MailSubject,MailBody,ReturnDays,ResendDays,ResendTimes,CrtUID,CrtDate,ScheduleType,SendDate,TopN,DeptNo1,DeptNo2,EquQty,AssetType,TakeOfficeDate,HouseID,Status,Monthst,Dayst,MailSubjectChn,MailSubjectEng,MailBodyChn,MailBodyEng)")
                sb.AppendLine("values (?SerNo,?ParamID,?MailSubject,?MailBody,")
                If ResendDays <> "" Then
                    sb.AppendLine("?ReturnDays,")
                Else
                    sb.AppendLine("null,")

                End If
                If ResendDays <> "" Then
                    sb.AppendLine("?ResendDays,")
                Else
                    sb.AppendLine("null,")

                End If
                If ResendTimes <> "" Then
                    sb.AppendLine("?ResendTimes,")
                Else
                    sb.AppendLine("null,")

                End If

                sb.AppendLine(" ?UID, now(), ?ScheduleType, ")
                If SendDate <> "" Then
                    sb.AppendLine("?SendDate,")
                Else
                    sb.AppendLine("null,")

                End If

                If TopN <> "" Then
                    sb.AppendLine("?TopN,")
                Else
                    sb.AppendLine("null,")

                End If
                sb.AppendLine("?DeptNo1, ?DeptNo2, ")

                If EquQty <> "" Then
                    sb.AppendLine("?EquQty,")
                Else
                    sb.AppendLine("null,")

                End If
                sb.AppendLine("?AssetType, ")


                If TakeOfficeDate <> "" Then
                    sb.AppendLine("?TakeOfficeDate,")
                Else
                    sb.AppendLine("null,")

                End If
                sb.AppendLine("?HouseID,?Status,?Monthst,?Dayst,?MailSubjectChn,?MailSubjectEng,?MailBodyChn,?MailBodyEng)")

                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?SerNo", max)
                cmd.Parameters.AddWithValue("?ParamID", ParamID)
                cmd.Parameters.AddWithValue("?MailSubject", MailSubject)
                cmd.Parameters.AddWithValue("?MailBody", MailBody)
                If ReturnDays <> "" Then
                    cmd.Parameters.AddWithValue("?ReturnDays", ReturnDays)
                Else
                    ' cmd.Parameters.AddWithValue("?ReturnDays", "null")
                End If
                If ResendDays <> "" Then
                    cmd.Parameters.AddWithValue("?ResendDays", ResendDays)
                Else
                    'cmd.Parameters.AddWithValue("?ResendDays", "null")
                End If
                If ResendTimes <> "" Then
                    cmd.Parameters.AddWithValue("?ResendTimes", ResendTimes)
                Else
                    '  cmd.Parameters.AddWithValue("?ResendTimes", "null")
                End If
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.Parameters.AddWithValue("?ScheduleType", ScheduleType)
                If SendDate <> "" Then
                    cmd.Parameters.AddWithValue("?SendDate", SendDate)
                Else
                    ' cmd.Parameters.AddWithValue("?SendDate", "null")
                End If
                If TopN <> "" Then
                    cmd.Parameters.AddWithValue("?TopN", TopN)
                Else
                    ' cmd.Parameters.AddWithValue("?TopN", "null")
                End If

                cmd.Parameters.AddWithValue("?DeptNo1", DeptNo1)
                cmd.Parameters.AddWithValue("?DeptNo2", DeptNo2)
                If EquQty <> "" Then
                    cmd.Parameters.AddWithValue("?EquQty", EquQty)
                Else
                    ' cmd.Parameters.AddWithValue("?EquQty", "null")
                End If
                cmd.Parameters.AddWithValue("?AssetType", AssetType)
                If TakeOfficeDate <> "" Then
                    cmd.Parameters.AddWithValue("?TakeOfficeDate", TakeOfficeDate)
                Else
                    ' cmd.Parameters.AddWithValue("?TakeOfficeDate", "null")
                End If
                cmd.Parameters.AddWithValue("?HouseID", HouseID)
                cmd.Parameters.AddWithValue("?Status", Status)
                cmd.Parameters.AddWithValue("?Monthst", Monthst)
                cmd.Parameters.AddWithValue("?Dayst", Dayst)
                cmd.Parameters.AddWithValue("?MailSubjectChn", MailSubjectChn)
                cmd.Parameters.AddWithValue("?MailSubjectEng", MailSubjectEng)
                cmd.Parameters.AddWithValue("?MailBodyChn", MailBodyChn)
                cmd.Parameters.AddWithValue("?MailBodyEng", MailBodyEng)
                cmd.ExecuteNonQuery()

            Catch ex As Exception

            Finally
                con.Close()

            End Try




        End Sub
        Public Sub UpdateSendMailParam(ByVal ParamID As String, ByVal MailSubject As String, ByVal MailBody As String, ByVal ReturnDays As String, ByVal ResendDays As String, ByVal ResendTimes As String _
                                               , ByVal UID As String, ByVal ScheduleType As String, ByVal SendDate As String, ByVal TopN As String, ByVal DeptNo1 As String, ByVal DeptNo2 As String, ByVal EquQty As String, _
                                                 ByVal AssetType As String, ByVal TakeOfficeDate As String, ByVal HouseID As String, ByVal Status As String, ByVal Monthst As String, ByVal Dayst As String, ByVal SerNo As String, _
                                                 ByVal MailSubjectChn As String, ByVal MailSubjectEng As String, ByVal MailBodyChn As String, ByVal MailBodyEng As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(20) {}
            'sqlParams(0) = New SqlParameter("@ParamID", SqlDbType.NVarChar, ParamID.Length)

            'sqlParams(0).Value = ParamID
            'sqlParams(1) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, MailSubject.Length)
            'sqlParams(1).Value = MailSubject
            'sqlParams(2) = New SqlParameter("@MailBody", SqlDbType.NVarChar, MailBody.Length)
            'sqlParams(2).Value = MailBody
            'sqlParams(3) = New SqlParameter("@ReturnDays", SqlDbType.Int)
            'If ReturnDays = "" Then

            '    sqlParams(3).Value = DBNull.Value
            'Else
            '    sqlParams(3).Value = ReturnDays
            'End If

            'sqlParams(4) = New SqlParameter("@ResendDays", SqlDbType.Int)
            'If ResendDays = "" Then

            '    sqlParams(4).Value = DBNull.Value
            'Else
            '    sqlParams(4).Value = ResendDays
            'End If
            'sqlParams(5) = New SqlParameter("@ResendTimes", SqlDbType.Int)
            'If ResendTimes = "" Then

            '    sqlParams(5).Value = DBNull.Value
            'Else
            '    sqlParams(5).Value = ResendTimes
            'End If

            'sqlParams(6) = New SqlParameter("@UID", SqlDbType.NVarChar, UID.Length)
            'sqlParams(6).Value = UID
            'sqlParams(7) = New SqlParameter("@ScheduleType", SqlDbType.NVarChar, ScheduleType.Length)
            'sqlParams(7).Value = ScheduleType
            'sqlParams(8) = New SqlParameter("@SendDate", SqlDbType.DateTime)
            'If SendDate = "" Then
            '    sqlParams(8).Value = DBNull.Value
            'Else
            '    sqlParams(8).Value = SendDate
            'End If

            'sqlParams(9) = New SqlParameter("@TopN", SqlDbType.Int)
            'If TopN = "" Then
            '    sqlParams(9).Value = DBNull.Value
            'Else
            '    sqlParams(9).Value = TopN
            'End If

            'sqlParams(10) = New SqlParameter("@DeptNo1", SqlDbType.NVarChar, DeptNo1.Length)
            'sqlParams(10).Value = DeptNo1
            'sqlParams(11) = New SqlParameter("@DeptNo2", SqlDbType.NVarChar, DeptNo2.Length)
            'sqlParams(11).Value = DeptNo2
            'sqlParams(12) = New SqlParameter("@EquQty", SqlDbType.Int)
            'If EquQty = "" Then
            '    sqlParams(12).Value = DBNull.Value
            'Else
            '    sqlParams(12).Value = EquQty
            'End If
            'sqlParams(13) = New SqlParameter("@AssetType", SqlDbType.NVarChar, AssetType.Length)
            'sqlParams(13).Value = AssetType


            'sqlParams(14) = New SqlParameter("@TakeOfficeDate", SqlDbType.DateTime)
            'If TakeOfficeDate = "" Then
            '    sqlParams(14).Value = DBNull.Value
            'Else
            '    sqlParams(14).Value = TakeOfficeDate
            'End If

            'sqlParams(15) = New SqlParameter("@HouseID", SqlDbType.NVarChar, HouseID.Length)
            'sqlParams(15).Value = HouseID
            'sqlParams(16) = New SqlParameter("@Status", SqlDbType.NVarChar, Status.Length)
            'sqlParams(16).Value = Status
            'sqlParams(17) = New SqlParameter("@Monthst", SqlDbType.NVarChar, Monthst.Length)
            'sqlParams(17).Value = Monthst
            'sqlParams(18) = New SqlParameter("@Dayst", SqlDbType.NVarChar, Dayst.Length)
            'sqlParams(18).Value = Dayst
            'sqlParams(19) = New SqlParameter("@SerNo", SqlDbType.NVarChar, Dayst.Length)
            'sqlParams(19).Value = SerNo

            'Dim sb As New StringBuilder()
            'sb.AppendLine(" update  OA_SendMail set ParamID=@ParamID,MailSubject=@MailSubject,MailBody=@MailBody,ReturnDays=@ReturnDays,ResendDays=@ResendDays,ResendTimes=@ResendTimes,CrtUID=@UID,CrtDate=getdate(),ScheduleType=@ScheduleType,")
            'sb.AppendLine(" SendDate=@SendDate,TopN=@TopN,DeptNo1=@DeptNo1,DeptNo2=@DeptNo2,EquQty=@EquQty,AssetType=@AssetType,TakeOfficeDate=@TakeOfficeDate,HouseID=@HouseID,Status=@Status,Monthst=@Monthst,Dayst=@Dayst")
            'sb.AppendLine(" where SerNo=@SerNo")

            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                Dim cmd As MySqlCommand
                sb.AppendLine(" update  OA_SendMail set ParamID=?ParamID,MailSubject=?MailSubject,MailBody=?MailBody,ReturnDays=?ReturnDays,ResendDays=?ResendDays,ResendTimes=?ResendTimes,ModiUID=?UID,ModiDate=now(),ScheduleType=?ScheduleType,")
                sb.AppendLine(" SendDate=?SendDate,TopN=?TopN,DeptNo1=?DeptNo1,DeptNo2=?DeptNo2,EquQty=?EquQty,AssetType=?AssetType,TakeOfficeDate=?TakeOfficeDate,HouseID=?HouseID,Status=?Status,Monthst=?Monthst,Dayst=?Dayst")
                sb.AppendLine(" ,MailSubjectChn=?MailSubjectChn,MailSubjectEng=?MailSubjectEng,MailBodyChn=?MailBodyChn,MailBodyEng=?MailBodyEng  where SerNo=?SerNo")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?ParamID", ParamID)
                cmd.Parameters.AddWithValue("?MailSubject", MailSubject)
                cmd.Parameters.AddWithValue("?MailBody", MailBody)
                If ReturnDays <> "" Then
                    cmd.Parameters.AddWithValue("?ReturnDays", ReturnDays)
                Else
                    cmd.Parameters.AddWithValue("?ReturnDays", DBNull.Value)
                End If
                If ResendDays <> "" Then
                    cmd.Parameters.AddWithValue("?ResendDays", ResendDays)
                Else
                    cmd.Parameters.AddWithValue("?ResendDays", DBNull.Value)
                End If
                If ResendTimes <> "" Then
                    cmd.Parameters.AddWithValue("?ResendTimes", ResendTimes)
                Else
                    cmd.Parameters.AddWithValue("?ResendTimes", DBNull.Value)
                End If
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.Parameters.AddWithValue("?ScheduleType", ScheduleType)
                If SendDate <> "" Then
                    cmd.Parameters.AddWithValue("?SendDate", SendDate)
                Else
                    cmd.Parameters.AddWithValue("?SendDate", DBNull.Value)
                End If
                If TopN <> "" Then
                    cmd.Parameters.AddWithValue("?TopN", TopN)
                Else
                    cmd.Parameters.AddWithValue("?TopN", DBNull.Value)
                End If

                cmd.Parameters.AddWithValue("?DeptNo1", DeptNo1)
                cmd.Parameters.AddWithValue("?DeptNo2", DeptNo2)
                If EquQty <> "" Then
                    cmd.Parameters.AddWithValue("?EquQty", EquQty)
                Else
                    cmd.Parameters.AddWithValue("?EquQty", DBNull.Value)
                End If
                cmd.Parameters.AddWithValue("?AssetType", AssetType)
                If TakeOfficeDate <> "" Then
                    cmd.Parameters.AddWithValue("?TakeOfficeDate", TakeOfficeDate)
                Else
                    cmd.Parameters.AddWithValue("?TakeOfficeDate", DBNull.Value)
                End If
                cmd.Parameters.AddWithValue("?HouseID", HouseID)
                cmd.Parameters.AddWithValue("?Status", Status)
                cmd.Parameters.AddWithValue("?Monthst", Monthst)
                cmd.Parameters.AddWithValue("?Dayst", Dayst)
                cmd.Parameters.AddWithValue("?MailSubjectChn", MailSubjectChn)
                cmd.Parameters.AddWithValue("?MailSubjectEng", MailSubjectEng)
                cmd.Parameters.AddWithValue("?MailBodyChn", MailBodyChn)
                cmd.Parameters.AddWithValue("?MailBodyEng", MailBodyEng)
                cmd.Parameters.AddWithValue("?SerNo", SerNo)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
            Finally
                con.Close()

            End Try


        End Sub

        ''' <summary>
        ''' 查詢發信條件檔資料
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetSendMail(ByVal ScheduleType As String, ByVal AssetType As String, ByVal DeptNo As String) As DataTable
            'Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
            'sqlParams(0) = New SqlParameter("@ScheduleType", SqlDbType.NVarChar, ScheduleType.Length)
            'sqlParams(0).Value = ScheduleType
            'sqlParams(1) = New SqlParameter("@AssetType", SqlDbType.NVarChar, AssetType.Length)
            'sqlParams(1).Value = AssetType
            'sqlParams(2) = New SqlParameter("@DeptNo", SqlDbType.NVarChar, DeptNo.Length)
            'sqlParams(2).Value = DeptNo


            'Dim sb As New StringBuilder()
            'sb.AppendLine("select case when Status='N' then 1 when Status='P' then 1 else 0 end as canModi ,  B.ParamDesc  ,A.* from OA_SendMail A left join OA_MailParam B on A.ParamID=B.ParamID  where A.DeptNo1 like @DeptNo+'%'   and A.AssetType like @AssetType +'%' and A.ScheduleType like @ScheduleType+'%'  ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("select Replace(convert(SendDate,char(10)),'-','/') as SendDate,case when Status='N' then 1 when Status='P' then 1 else 0 end as canModi ,  B.ParamDesc  ,A.* from OA_SendMail A left join OA_MailParam B on A.ParamID=B.ParamID  where A.DeptNo1 like '" + DeptNo + "%'   and A.AssetType like '" + AssetType + "%' and A.ScheduleType like '" + ScheduleType + "%'  ")
                cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?DeptNo", DeptNo)
                'cmd.Parameters.AddWithValue("?AssetType", AssetType)
                'cmd.Parameters.AddWithValue("?ScheduleType", ScheduleType)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try
        End Function


        ''' <summary>
        ''' 查詢發送的信件
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetSendMailbySerno(ByVal SerNo As Integer) As DataTable
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@SerNo", SqlDbType.Int)
            'sqlParams(0).Value = SerNo


            'Dim sb As New StringBuilder()
            'sb.AppendLine("select replace(convert(varchar(10),SendDate,120),'-','/') as SendDate, B.ParamDesc  ,A.* from OA_SendMail A left join OA_MailParam B on A.ParamID=B.ParamID  where A.SerNo=@SerNo")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("select Replace(convert(SendDate,char(10)),'-','/') as SendDate, B.ParamDesc  ,A.* from OA_SendMail A left join OA_MailParam B on A.ParamID=B.ParamID  where A.SerNo=?SerNo")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?SerNo", SerNo)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try
        End Function


        ''' <summary>
        ''' 刪除某一筆資料
        ''' </summary>       
        ''' <remarks></remarks>
        Public Sub DeleteSendMail(ByVal SerNo As Integer, ByVal UID As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@SerNo", SqlDbType.Int)
            'sqlParams(0).Value = SerNo
            'Dim sb As New StringBuilder()
            'sb.AppendLine("Update OA_SendMail set Status='D' where SerNo=@SerNo  ")
            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                sb.AppendLine("Update OA_SendMail set Status='D',ModiUID=?UID ,ModiDate=now()  where SerNo=?SerNo  ")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?SerNo", SerNo)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
            Finally
                con.Close()

            End Try



        End Sub

        ''' <summary>
        ''' 修改信件發送條件狀態
        ''' </summary>
        ''' <param name="SerNo">流水編號</param>
        ''' <param name="Status">狀態</param>
        ''' <remarks></remarks>
        Public Sub UpdateSendMail(ByVal SerNo As Integer, ByVal Status As String, ByVal UID As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@SerNo", SqlDbType.Int)
            'sqlParams(0).Value = SerNo
            'sqlParams(1) = New SqlParameter("@Status", SqlDbType.NVarChar, Status.Length)
            'sqlParams(1).Value = Status
            'Dim sb As New StringBuilder()
            'sb.AppendLine("Update OA_SendMail set Status=@Status ,ModiUID=?UID ,ModiDate=now() where SerNo=@SerNo  ")
            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@SerNo", SqlDbType.Int)
            'sqlParams(0).Value = SerNo
            'Dim sb As New StringBuilder()
            'sb.AppendLine("Update OA_SendMail set Status='D' where SerNo=@SerNo  ")
            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                sb.AppendLine("Update OA_SendMail set Status=?Status,ModiUID=?UID ,ModiDate=now()  where SerNo=?SerNo  ")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?SerNo", SerNo)
                cmd.Parameters.AddWithValue("?Status", Status)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
            Finally
                con.Close()

            End Try


        End Sub
    End Class



    Public Class MailParamDAO
        Inherits OAMS.DAO.BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        ''' <summary>
        ''' 取得郵件參數設定資料
        ''' </summary>
        ''' <returns>取得郵件參數設定資料</returns>
        ''' <remarks>取得郵件參數設定資料</remarks>
        Public Function GetMailParam() As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


            'Dim sb As New StringBuilder()

            'sb.AppendLine("Select  A.*,B.UserName as CrtName ,C.UserName as ModiName  from OA_MailParam A left join Users B on A.CrtUID=B.UserID  left join Users C on A.ModiUID=C.UserID ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
            Dim sb As New StringBuilder()
            sb.AppendLine(" Select  A.*,'' as CrtName ,'' as ModiName  from OA_MailParam A  ")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                For Each dr As DataRow In oTable.Rows
                    dr("CrtName") = uDao.GetUserName(dr("CrtUID").ToString)
                    dr("ModiName") = uDao.GetUserName(dr("ModiUID").ToString)
                Next


                Return oTable
            Catch ex As Exception
            Finally
                con.Close()
            End Try

        End Function


        ''' <summary>
        ''' 取得郵件參數設定代號資料
        ''' </summary>
        ''' <param name="kind">0=一般,1=全部</param>
        ''' <returns>取得郵件參數設定代號資料</returns>
        ''' <remarks>取得郵件參數設定代號資料</remarks>
        Public Function GetMailParamItem(ByVal kind As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


            'Dim sb As New StringBuilder()
            'If kind = "1" Then
            '    sb.AppendLine("Select ParamID,ParamDesc  from OA_MailParam  ")
            'Else

            '    sb.AppendLine("Select ParamID,ParamDesc  from OA_MailParam  where ParamID>2")
            'End If
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

            Dim sb As New StringBuilder()
            If kind = "1" Then
                sb.AppendLine("Select ParamID,ParamDesc  from OA_MailParam  ")
            Else

                sb.AppendLine("Select ParamID,ParamDesc  from OA_MailParam  where ParamID>10")
            End If
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()
            End Try

        End Function
        ''' <summary>
        ''' 取得1筆郵件參數設定資料
        ''' </summary>
        ''' <returns>取得1筆郵件參數設定資料</returns>
        ''' <remarks>取得1筆郵件參數設定資料</remarks>
        Public Function GetMailParamByID(ByVal ParamID As String) As DataTable
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            'sqlParams(0) = New SqlParameter("@ParamID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = ParamID

            'Dim sb As New StringBuilder()
            'sb.AppendLine("Select Replace(convert(varchar(20),CrtDate,120),'-',',') as CrtDate,replace(convert(varchar(20),ModiDate,120),'-','/') as ModiDate, A.*,B.UserName as CrtName ,C.UserName as ModiName  from OA_MailParam A left join Users B on A.CrtUID=B.UserID  left join Users C on A.ModiUID=C.UserID where ParamID=@ParamID ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            Dim sb As New StringBuilder()
            sb.AppendLine(" Select Replace(convert(CrtDate,char(20)),'-','/') as CrtDate,replace(convert(ModiDate,char(20)),'-','/') as ModiDate, A.*,'' as CrtName, '' as ModiName  from OA_MailParam A  where ParamID=?ParamID")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Try
                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?ParamID", ParamID)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                For Each dr As DataRow In oTable.Rows
                    dr("CrtName") = uDao.GetUserName(dr("CrtUID").ToString)
                    dr("ModiName") = uDao.GetUserName(dr("ModiUID").ToString)
                Next
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()
            End Try
        End Function


        ''' <summary>
        ''' 新增1筆空白並回傳ID
        ''' </summary>
        ''' <returns>新增1筆空白並回傳ID</returns>
        ''' <remarks>新增1筆空白並回傳ID</remarks>

        Public Function InsertMailParam(ByVal UID As String) As String
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            'sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = UID

            'Dim sb As New StringBuilder()
            'sb.AppendLine("declare @ParamID int select @ParamID=(convert(int,max(ParamID)) +1) from OA_MailParam ")
            'sb.AppendLine("Insert into OA_MailParam (ParamID ,ParamDesc ,MailSubject,MailBody,ReturnDays,ResendDays,CrtUID,ResendTimes) values (isNull(@ParamID,1),'','','',0,0,@UID,0)  select ParamID=isNull(@ParamID,1)")
            'Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString()


            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                sb.AppendLine(" select max(ParamID)+1 as MAX1 from OA_MailParam ")

                Dim oTable As Data.DataTable
                Dim cmd As MySqlCommand
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                sb.Length = 0
                sb.AppendLine("Insert into OA_MailParam (ParamID ,ParamDesc ,MailSubject,MailBody,ReturnDays,ResendDays,CrtUID,ResendTimes,CrtDate,ModiUID) values (?ParamID,'','','',0,0,?UID,0,?aDate,'')  ")

                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?ParamID", max)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.Parameters.AddWithValue("?aDate", DateTime.Now)
                cmd.ExecuteNonQuery()
                Return max
            Catch ex As Exception
            Finally
                con.Close()

            End Try



        End Function


        Public Sub UpdateMailParam(ByVal ParamID As String, ByVal ParamDesc As String, ByVal ReturnDays As Integer, ByVal ResendDays As Integer, ByVal ResendTimes As Integer, ByVal MailSubject As String, ByVal MailBody As String, ByVal UID As String, ByVal MailSubjectChn As String, ByVal MailSubjectEng As String, ByVal MailBodyChn As String, ByVal MailBodyEng As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(8) {}

            'sqlParams(0) = New SqlParameter("@ParamDesc", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = ParamDesc
            'sqlParams(1) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, 100)
            'sqlParams(1).Value = MailSubject
            'sqlParams(2) = New SqlParameter("@MailBody", SqlDbType.NVarChar, MailBody.Length)
            'sqlParams(2).Value = MailBody
            'sqlParams(3) = New SqlParameter("@ParamID", SqlDbType.NVarChar, 100)
            'sqlParams(3).Value = ParamID
            'sqlParams(4) = New SqlParameter("@ReturnDays", SqlDbType.Int)
            'sqlParams(4).Value = ReturnDays
            'sqlParams(5) = New SqlParameter("@ResendDays", SqlDbType.Int)
            'sqlParams(5).Value = ResendDays
            'sqlParams(6) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(6).Value = UID
            'sqlParams(7) = New SqlParameter("@ResendTimes", SqlDbType.Int)
            'sqlParams(7).Value = ResendTimes
            'Dim sb As New StringBuilder()

            'sb.AppendLine("update  OA_MailParam set ParamDesc=@ParamDesc ,MailSubject=@MailSubject ,MailBody=@MailBody,ReturnDays=@ReturnDays,ResendDays=@ResendDays,ModiUID=@UID,ModiDate=GetDate(),ResendTimes=@ResendTimes where ParamID= @ParamID ")
            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)


            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                sb.AppendLine("update  OA_MailParam set ParamDesc=?ParamDesc ,MailSubject=?MailSubject ,MailBody=?MailBody,ReturnDays=?ReturnDays,ResendDays=?ResendDays,ModiUID=?UID,ModiDate=?aDate,ResendTimes=?ResendTimes")
                sb.AppendLine(" ,MailSubjectChn=?MailSubjectChn,MailSubjectEng=?MailSubjectEng,MailBodyChn=?MailBodyChn ,MailBodyEng=?MailBodyEng where ParamID= ?ParamID  ")
                Dim cmd As MySqlCommand
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?ParamDesc", ParamDesc)
                cmd.Parameters.AddWithValue("?MailSubject", MailSubject)
                cmd.Parameters.AddWithValue("?MailBody", MailBody)
                cmd.Parameters.AddWithValue("?ReturnDays", ReturnDays)
                cmd.Parameters.AddWithValue("?ResendDays", ResendDays)
                cmd.Parameters.AddWithValue("?UID", UID)
                cmd.Parameters.AddWithValue("?aDate", DateTime.Now)
                cmd.Parameters.AddWithValue("?ResendTimes", ResendTimes)
                cmd.Parameters.AddWithValue("?MailSubjectChn", MailSubjectChn)
                cmd.Parameters.AddWithValue("?MailSubjectEng", MailSubjectEng)
                cmd.Parameters.AddWithValue("?MailBodyChn", MailBodyChn)
                cmd.Parameters.AddWithValue("?MailBodyEng", MailBodyEng)
                cmd.Parameters.AddWithValue("?ParamID", ParamID)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
            Finally
                con.Close()

            End Try



        End Sub



    End Class

End Namespace
